﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Globalization;
using System.IO;
using System.Diagnostics;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using VietKinsNutrition.Common;
using VietKinsNutrition.Data;

namespace VietKinsNutrition.Bussiness
{
    public class ClsDanhsachTre
    {

        #region VarPublic
        public static string SaveResouceFile(string FileName, Stream stream)
        {
            string FilePath = Path.GetTempPath() + FileName;
            FileStream fs = new FileStream(FilePath, FileMode.Create);
            BinaryReader br = new BinaryReader(stream);
            byte[] save = new byte[stream.Length];
            br.Read(save, 0, save.Length);
            BinaryWriter bw = new BinaryWriter(fs);
            bw.Write(save, 0, save.Length);
            bw.Flush();
            bw.Close();
            return FilePath;
        }
        public static void KillExcelProcess()
        {
            try
            {
                foreach (Process thisproc in Process.GetProcessesByName("excel"))
                {
                    if (!thisproc.CloseMainWindow())
                    {
                        thisproc.Kill();
                    }
                }
            }
            catch (Exception Exc)
            {
                Exc.Source = "";
            }
        }





        #endregion
        #region FunctionPublic_Report
        #region Private

        public static void Danhsachtre(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, System.Data.DataTable Tblcot)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                oXL.Visible = true;
                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_danhsachtre(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien, Tblcot);
            }
            catch
            {
            }
        }
        public static void Ds_tredihoc(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_ds_tredihoc(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien);
                oXL.Visible = true;
            }
            catch
            {
            }
        }
        public static void Ds_tresuckhoe(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string lop)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_ds_tresuckhoe(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien, lop);
                oXL.Visible = true;
            }
            catch
            {
            }
        }
        public static void Ds_trenhanthuc(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_ds_trenhanthuc(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien);
                oXL.Visible = true;
            }
            catch
            {
            }
        }
        //    System.Globalization.CultureInfo systemCultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture;
        //System.Globalization.CultureInfo fc = new System.Globalization.CultureInfo(0x0409, true);
        //fc.NumberFormat.NumberDecimalSeparator = ",";
        //fc.NumberFormat.NumberGroupSeparator = ".";
        //System.Threading.Thread.CurrentThread.CurrentCulture = fc;
        //System.Threading.Thread.CurrentThread.CurrentUICulture = fc;
        public static void Ds_tre_thunam(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string lop)
        {

            try
            {

                //System.Globalization.CultureInfo m_CurrentCulture = System.Threading.Thread.CurrentThread.CurrentCulture;
                //System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                //System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.NumberGroupSeparator = ".";
                //System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.NumberDecimalSeparator = ",";
                //System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture;


                KillExcelProcess();
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);

                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();


                oXL = new ApplicationClass();


                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];

                Execute_ds_tre_thunam(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien, lop);
                oXL.Visible = true;




            }
            catch
            {
            }
        }
        public static void Ds_tre_thukhac(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string tenlop)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_ds_tre_thukhac(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien, tenlop);
                oXL.Visible = true;
            }
            catch
            {
            }
        }
        public static void Ds_tre_phieuan(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string lop)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_ds_tre_phieuan(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien, lop);
                oXL.Visible = true;
            }
            catch
            {
            }
        }
        public static void Bc_suckhoe(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_Bc_suckhoe(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien);
                oXL.Visible = true;
            }
            catch
            {
            }
        }



        private static void Execute_danhsachtre(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, System.Data.DataTable Tblcot)
        {
            string StartChar;
            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;
                //   if (ClsConnection.DBName == "DbPhong")
                //          oSheet.get_Range("C4", "C4").Value2 = tieude;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                //         oSheet.get_Range("C5", "C5").Value2 = "Ngày báo cáo: " + time;

                string Start = "A";
                string End = ExcelFunc.NextChar(Start, TblBaocaothongke.Columns.Count - 1);

                Microsoft.Office.Interop.Excel.Range rang;
                rang = oSheet.get_Range(Start + "4", End + "4");
                rang.Merge(false);
                rang.Font.Size = "16";
                rang.Value2 = tieude;
                rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

                rang = oSheet.get_Range(Start + "5", End + "5");
                rang.Merge(false);
                rang.Value2 = "Ngày báo cáo: " + time;
                rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;


                string colStart = "A";
                for (int i = 0; i < Tblcot.Rows.Count; i++)
                {
                    if (bool.Parse(Tblcot.Rows[i]["trangthai"].ToString()) == false)
                    {
                        Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)oSheet.get_Range(colStart + "6", colStart + (int.Parse(Tblnhanvien.Rows.Count.ToString()) + 6).ToString());
                        range.Delete(XlDeleteShiftDirection.xlShiftToLeft);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
                    }
                    else
                        colStart = ExcelFunc.NextChar(colStart);
                }

                int j = 6;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A7", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }

                for (int m = 1; m <= TblBaocaothongke.Columns.Count; m++)
                {
                    AutoFitColumn(m, oSheet);
                }

            }
            catch (Exception ex)
            { }
        }
        private static void Execute_ds_tredihoc(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien)
        {

            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                oSheet.get_Range("A4", "A4").Value2 = tieude;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Ngày báo cáo: " + time;


                int j = 6;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A7", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }

                //for (int m = 1; m <= TblBaocaothongke.Columns.Count; m++)
                //{
                //    AutoFitColumn(m, oSheet);
                //}

            }
            catch (Exception ex)
            { }
        }
        private static void Execute_ds_tresuckhoe(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string lop)
        {

            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;
            int cn_bt = 0;
            int cn_sdd1 = 0; int cn_sdd2 = 0; int cn_ncbp1 = 0; int cn_ncbp2 = 0; int cc_bt = 0; int cc_tc1 = 0; int cc_tc2 = 0;
            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                oSheet.get_Range("A4", "A4").Value2 = tieude;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Lớp: " + lop;
                oSheet.get_Range("A6", "A6").Value2 = "Ngày báo cáo: " + time;
                bool tg = true;

                int j = 8;
                string a = "A";
                Microsoft.Office.Interop.Excel.Range rang;
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A9", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        rang = oSheet.get_Range(a + j.ToString(), a + j.ToString());
                        if (tg == false)
                        {
                            rang.Interior.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.LightYellow);
                        }
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                    if (tg == true)
                        tg = false;
                    else
                        tg = true;
                    #region Tổng kết SK
                    if (TblBaocaothongke.Rows[i]["kqcannang"].ToString() == "K - BT")
                        cn_bt += 1;
                    if (TblBaocaothongke.Rows[i]["kqcannang"].ToString() == "K - SDD vừa")
                        cn_sdd1 += 1;
                    if (TblBaocaothongke.Rows[i]["kqcannang"].ToString() == "K - SDD nặng")
                        cn_sdd2 += 1;
                    if (TblBaocaothongke.Rows[i]["kqcannang"].ToString() == "K - NCBP độ 1")
                        cn_ncbp1 += 1;
                    if (TblBaocaothongke.Rows[i]["kqcannang"].ToString() == "K - NCBP độ 2")
                        cn_ncbp2 += 1;
                    if (TblBaocaothongke.Rows[i]["kqchieucao"].ToString() == "K - TC độ 2")
                        cc_tc2 += 1;
                    if (TblBaocaothongke.Rows[i]["kqchieucao"].ToString() == "K - TC độ 1")
                        cc_tc1 += 1;
                    if (TblBaocaothongke.Rows[i]["kqchieucao"].ToString() == "K - BT")
                        cc_bt += 1;

                    #endregion

                    rang = oSheet.get_Range("K" + j.ToString(), "M" + j.ToString());
                    rang.Merge(false);
                }
                AutoFitColumn(7, oSheet);
                AutoFitColumn(9, oSheet);
                //for (int m = 1; m <= TblBaocaothongke.Columns.Count; m++)
                //{
                //    AutoFitColumn(m, oSheet);
                //}

                //       if (tieude == "SỔ KHÁM SỨC KHỎE")
                //         {
                oSheet.get_Range("F" + (11 + TblBaocaothongke.Rows.Count), "F" + (11 + TblBaocaothongke.Rows.Count)).Value2 = cc_tc2;
                oSheet.get_Range("G" + (11 + TblBaocaothongke.Rows.Count), "G" + (11 + TblBaocaothongke.Rows.Count)).Value2 = cc_tc1;
                oSheet.get_Range("H" + (11 + TblBaocaothongke.Rows.Count), "H" + (11 + TblBaocaothongke.Rows.Count)).Value2 = cc_bt;
                oSheet.get_Range("I" + (11 + TblBaocaothongke.Rows.Count), "I" + (11 + TblBaocaothongke.Rows.Count)).Value2 = cn_sdd2;
                oSheet.get_Range("J" + (11 + TblBaocaothongke.Rows.Count), "J" + (11 + TblBaocaothongke.Rows.Count)).Value2 = cn_sdd1;
                oSheet.get_Range("K" + (11 + TblBaocaothongke.Rows.Count), "K" + (11 + TblBaocaothongke.Rows.Count)).Value2 = cn_bt;
                oSheet.get_Range("L" + (11 + TblBaocaothongke.Rows.Count), "L" + (11 + TblBaocaothongke.Rows.Count)).Value2 = cn_ncbp1;
                oSheet.get_Range("M" + (11 + TblBaocaothongke.Rows.Count), "M" + (11 + TblBaocaothongke.Rows.Count)).Value2 = cn_ncbp2;
                //        }
                oSheet.PageSetup.CenterHorizontally = true;

            }
            catch (Exception ex)
            { }
        }
        private static void Execute_ds_trenhanthuc(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien)
        {

            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                oSheet.get_Range("A4", "A4").Value2 = tieude;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Ngày báo cáo: " + time;


                int j = 6;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A7", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }

                //for (int m = 1; m <= TblBaocaothongke.Columns.Count; m++)
                //{
                //    AutoFitColumn(m, oSheet);
                //}

            }
            catch (Exception ex)
            { }
        }
        private static void Execute_ds_tre_thunam(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string lop)
        {

            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                oSheet.get_Range("A4", "A4").Value2 = tieude;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Lớp: " + lop;

                oSheet.get_Range("A6", "A6").Value2 = "Ngày báo cáo: " + time;
                int j = 7;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A8", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;

                NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat;
                nfi.NumberGroupSeparator = ".";
                nfi.NumberDecimalSeparator = ",";
                double tongtien1 = 0; double tongtien2 = 0; double tongtien3 = 0;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        //         oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];

                        double s; string tg = ""; string test = "0";
                        if (TblBaocaothongke.Rows[i][b].ToString() != "")
                            test = TblBaocaothongke.Rows[i][b].ToString();
                        if (a == "F" || a == "H" || a == "I" || a == "J")
                        {
                            s = double.Parse(test);
                            tg = s.ToString("N0", nfi);
                            oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = tg;

                        }
                        else
                            oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];

                        if (a == "H")
                        {
                            tongtien1 += int.Parse(test);
                        }
                        if (a == "I")
                        {
                            tongtien2 += int.Parse(test);
                        }
                        if (a == "J")
                        {
                            tongtien3 += int.Parse(test);
                        }
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }

                Range rang = oSheet.get_Range("B" + (9 + TblBaocaothongke.Rows.Count).ToString(), "B" + (9 + TblBaocaothongke.Rows.Count).ToString());
                //rang.Value2 = "Tổng tiền:  ";
                //rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
                //rang.Font.Bold = true;
                // rang.RowHeight = 24;

                rang = oSheet.get_Range("H" + (8 + TblBaocaothongke.Rows.Count).ToString(), "H" + (8 + TblBaocaothongke.Rows.Count).ToString());
                //        rang.Value2 = "=SUM(H8:H" + (7 + TblBaocaothongke.Rows.Count).ToString() + ")";
                rang.Value2 = tongtien1.ToString("N0", nfi);

                rang = oSheet.get_Range("I" + (8 + TblBaocaothongke.Rows.Count).ToString(), "I" + (8 + TblBaocaothongke.Rows.Count).ToString());
                //           rang.Value2 = "=SUM(I8:I" + (7 + TblBaocaothongke.Rows.Count).ToString() + ")";
                rang.Value2 = tongtien2.ToString("N0", nfi);

                rang = oSheet.get_Range("J" + (8 + TblBaocaothongke.Rows.Count).ToString(), "J" + (8 + TblBaocaothongke.Rows.Count).ToString());
                //           rang.Value2 = "=SUM(J8:J" + (7 + TblBaocaothongke.Rows.Count).ToString() + ")";
                rang.Value2 = tongtien3.ToString("N0", nfi);

                //for (int m = 1; m <= TblBaocaothongke.Columns.Count; m++)
                //{
                //    AutoFitColumn(m, oSheet);
                //}

            }
            catch (Exception ex)
            { }
        }
        private static void Execute_ds_tre_thukhac(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string tenlop)
        {

            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                oSheet.get_Range("B4", "B4").Value2 = tieude;
                oSheet.get_Range("a5", "a5").Value2 = "Lớp:  " + tenlop;
                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A6", "A6").Value2 = "Ngày báo cáo: " + time;


                int j = 7;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A8", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;

                NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat;
                nfi.NumberGroupSeparator = ".";
                nfi.NumberDecimalSeparator = ",";
                int tongtien1 = 0;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        int s; string tg = ""; string test = "0";
                        if (TblBaocaothongke.Rows[i][b].ToString() != "")
                            test = TblBaocaothongke.Rows[i][b].ToString();
                        if (a == "F")
                        {
                            s = int.Parse(test);
                            tongtien1 += s;
                            //tg = s.ToString("N0", nfi);

                            oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = ((ulong.Parse(test)).ToString("#,##0")).Replace(',', '.');


                        }
                        else
                            oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];

                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }


                Range rang = oSheet.get_Range("E" + (8 + TblBaocaothongke.Rows.Count).ToString(), "E" + (8 + TblBaocaothongke.Rows.Count).ToString());
                rang.Value2 = "Tổng tiền:  ";
                rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
                rang.Font.Bold = true;
                rang.RowHeight = 24;

                rang = oSheet.get_Range("F" + (8 + TblBaocaothongke.Rows.Count).ToString(), "F" + (8 + TblBaocaothongke.Rows.Count).ToString());
                rang.Value2 = tongtien1.ToString("N0", nfi);
            }
            catch (Exception ex)
            { }
        }
        private static void Execute_ds_tre_phieuan(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string lop)
        {

            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                oSheet.get_Range("A4", "A4").Value2 = tieude;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Lớp: " + lop;
                oSheet.get_Range("A6", "A6").Value2 = "Ngày báo cáo: " + time;

                int j = 7;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A8", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;

                NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat;
                nfi.NumberGroupSeparator = ".";
                nfi.NumberDecimalSeparator = ",";

                Range rang;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        int s; string tg = ""; string test = "0";
                        if (TblBaocaothongke.Rows[i][b].ToString() != "")
                            test = TblBaocaothongke.Rows[i][b].ToString();

                        if (a == "G")
                        {
                            s = int.Parse(test);
                            tg = s.ToString("N0", nfi);
                            rang = oSheet.get_Range(a + j.ToString(), a + j.ToString());
                            rang.Value2 = "'" + tg;
                            rang.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;

                        }
                        else
                            oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }
            }
            catch (Exception ex)
            { }
        }
        private static void Execute_Bc_suckhoe(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien)
        {

            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                oSheet.get_Range("A4", "A4").Value2 = tieude;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Ngày báo cáo: " + time;


                int j = 6;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A7", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }
            }
            catch (Exception ex)
            { }
        }


        private static void AutoFitColumn(int column, Microsoft.Office.Interop.Excel._Worksheet sheet)
        {
            ((Range)sheet.Cells[1, column]).EntireColumn.AutoFit();
        }

        public static void Dstre_phieuan_(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string lop, string trangthai)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Executeds_tre_phieuan(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien, lop, trangthai);
                oXL.Visible = true;
            }
            catch
            {
            }
        }

        private static void Executeds_tre_phieuan(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string lop, string trangthai)
        {

            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                oSheet.get_Range("A4", "A4").Value2 = tieude;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Lớp: " + lop;

                oSheet.get_Range("A6", "A6").Value2 = "Ngày báo cáo: " + time;
                int j = 7;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A8", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }

                Range rang = oSheet.get_Range("B" + (9 + TblBaocaothongke.Rows.Count).ToString(), "B" + (9 + TblBaocaothongke.Rows.Count).ToString());
                
                rang = oSheet.get_Range("B" + (9 + TblBaocaothongke.Rows.Count).ToString(), "e" + (9 + TblBaocaothongke.Rows.Count).ToString());
                rang.Merge(false);
                rang.Value2 = "Số trẻ " + trangthai.ToLower() + " phiếu ăn: " + TblBaocaothongke.Rows.Count + " trẻ ";
                rang.Font.Bold = true;
                AutoFitColumn(2, oSheet);
                AutoFitColumn(5, oSheet);
                AutoFitColumn(6, oSheet);
                AutoFitColumn(7, oSheet);
            }
            catch (Exception ex)
            { }
        }
        public static void Ds_diemdanh_thang(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string lop)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_ds_diemdanh_thang(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien, lop);
                oXL.Visible = true;
            }
            catch
            {
            }
        }
        private static void Execute_ds_diemdanh_thang(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string lop)
        {

            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;
            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                oSheet.get_Range("A4", "A4").Value2 = tieude;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Lớp: " + lop;
                oSheet.get_Range("A6", "A6").Value2 = "Ngày báo cáo: " + time;


                int j = 7;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A8", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1) && i != (TblBaocaothongke.Rows.Count - 2))
                    {
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);
                    }

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";

                }
            }
            catch (Exception ex)
            { }
        }


        public static void CV_tre(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, string lop, int id)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_CV_tre(oSheet, tenso, tenphong, tentruong, tieude, lop, id);
                oXL.Visible = true;
            }
            catch
            {
            }
        }

        private static void Execute_CV_tre(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, string lop, int id)
        {

            ClsBaocaotonghopDAL ClsBaocaotonghopdal = new ClsBaocaotonghopDAL();
            System.Data.DataTable TblBaocaothongke1 = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT TblTre.Hovaten, TblTre.Tenthuonggoi, TblTre.Ngaysinh, TblTre.Noiohiennay, TblTre.Gioitinh, TblTre.Hotenbo, TblTre.Nghenghiepbo, TblTre.Hotenme, TblTre.Nghengheme, TblTre.sodt, TblDm_Uutien.Tenuutien, TblDm_Quoctich.Tenquoctich, TblDm_Tongiao.Tentongiao, TblDm_Dantoc.Tendantoc, TblTre.Ngaynhaphoc, TblTre.Thoiquentre, TblTre.Emailph, TblTre.Id FROM (((TblTre INNER JOIN TblDm_Dantoc ON TblTre.DantocId = TblDm_Dantoc.Id) INNER JOIN TblDm_Quoctich ON TblTre.QuoctichID = TblDm_Quoctich.Id) INNER JOIN TblDm_Tongiao ON TblTre.TongiaoID = TblDm_Tongiao.Id) INNER JOIN TblDm_Uutien ON TblTre.UutienId = TblDm_Uutien.Id WHERE (((TblTre.Id)=" + id + ")) ");
            System.Data.DataTable TblBaocaothongke = TblBaocaothongke1;
            System.Data.DataTable Tblsuckhoe = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT top 1 TblTrekiemtrasuckhoe.KqCannang, TblTrekiemtrasuckhoe.KqChieucao, TblDm_Thanghoc.Nam, TblDm_Thanghoc.Thang FROM TblDm_Thanghoc INNER JOIN (TblTrekiemtrasuckhoe INNER JOIN TblNamtrehoc ON TblTrekiemtrasuckhoe.NamtrehocId = TblNamtrehoc.Id) ON TblDm_Thanghoc.Id = TblTrekiemtrasuckhoe.ThanghocId WHERE (((TblNamtrehoc.TreId)=" + id + ")) and Tblnamtrehoc.namhocid in (select id from tbldm_namhoc where trangthai =-1)  order by nam desc, thang desc");

            try
            {
                string s = ""; string s1 = "";
                DateTime time;
                if (TblBaocaothongke.Rows[0]["ngaysinh"].ToString() != "")
                {
                    time = DateTime.Parse(TblBaocaothongke.Rows[0]["ngaysinh"].ToString());
                    s = time.ToString("dd/MM/yyyy");
                }
                if (TblBaocaothongke.Rows[0]["ngaynhaphoc"].ToString() != "")
                {
                    time = DateTime.Parse(TblBaocaothongke.Rows[0]["ngaynhaphoc"].ToString());
                    s1 = time.ToString("dd/MM/yyyy");
                }

                oSheet.get_Range("F3", "F3").Value2 = TblBaocaothongke.Rows[0]["Hovaten"].ToString().ToUpper();
                oSheet.get_Range("F4", "F4").Value2 = TblBaocaothongke.Rows[0]["Tenthuonggoi"];
                oSheet.get_Range("F5", "F5").Value2 = s;
                oSheet.get_Range("H5", "H5").Value2 = TblBaocaothongke.Rows[0]["gioitinh"];
                oSheet.get_Range("F6", "F6").Value2 = TblBaocaothongke.Rows[0]["Noiohiennay"];
                oSheet.get_Range("F8", "F8").Value2 = TblBaocaothongke.Rows[0]["Tenquoctich"];
                oSheet.get_Range("F9", "F9").Value2 = TblBaocaothongke.Rows[0]["Tendantoc"];
                oSheet.get_Range("H9", "H9").Value2 = TblBaocaothongke.Rows[0]["Tentongiao"];
                oSheet.get_Range("E11", "E11").Value2 = TblBaocaothongke.Rows[0]["Hotenbo"];
                oSheet.get_Range("H11", "H11").Value2 = TblBaocaothongke.Rows[0]["nghenghiepbo"];
                oSheet.get_Range("E12", "E12").Value2 = TblBaocaothongke.Rows[0]["Hotenme"];
                oSheet.get_Range("H12", "H12").Value2 = TblBaocaothongke.Rows[0]["Nghengheme"];
                oSheet.get_Range("E13", "E13").Value2 = "'" + TblBaocaothongke.Rows[0]["Sodt"];
                oSheet.get_Range("E14", "E14").Value2 = TblBaocaothongke.Rows[0]["Emailph"];
                oSheet.get_Range("E15", "E15").Value2 = s1;
                oSheet.get_Range("E16", "E16").Value2 = TblBaocaothongke.Rows[0]["Tenuutien"];
                oSheet.get_Range("E18", "E18").Value2 = TblBaocaothongke.Rows[0]["thoiquentre"];
                if (Tblsuckhoe.Rows.Count > 0)
                    oSheet.get_Range("E17", "E17").Value2 = Tblsuckhoe.Rows[0]["kqchieucao"] + ", " + Tblsuckhoe.Rows[0]["kqcannang"];
            }
            catch (Exception ex)
            { }
        }



        #endregion
        #endregion

        public static void Bc_baoan(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, int tre_mg, int tre_nt, int mg_ba, int nt_ba)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_Bc_baoan(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien, tre_mg, tre_nt, mg_ba, nt_ba);
                oXL.Visible = true;
            }
            catch
            {
            }
        }
        private static void Execute_Bc_baoan(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, int tre_mg, int tre_nt, int mg_ba, int nt_ba)
        {

            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                oSheet.get_Range("A4", "A4").Value2 = tieude;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Ngày báo cáo: " + time;

                int j = 6;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A7", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                }
                oSheet.get_Range("C" + (7 + TblBaocaothongke.Rows.Count), "C" + (7 + TblBaocaothongke.Rows.Count)).Value2 = "=sum(C7:C" + (6 + TblBaocaothongke.Rows.Count) + ")";
                oSheet.get_Range("D" + (7 + TblBaocaothongke.Rows.Count), "D" + (7 + TblBaocaothongke.Rows.Count)).Value2 = "=sum(D7:D" + (6 + TblBaocaothongke.Rows.Count) + ")";

            }
            catch (Exception ex)
            { }
        }

        public static void Bc_baoan(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, int tre_mg, int tre_nt, int mg_ba, int nt_ba, int thangid, DateTime ngay, int namid)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
                Execute_Bc_baoan(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien, tre_mg, tre_nt, mg_ba, nt_ba, thangid, ngay, namid);
                oXL.Visible = true;
            }
            catch
            {
            }
        }
        private static void Execute_Bc_baoan(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, int tre_mg, int tre_nt, int mg_ba, int nt_ba, int thang, DateTime ngay, int namid)
        {

            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;

            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                oSheet.get_Range("A4", "A4").Value2 = tieude;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
                oSheet.get_Range("A5", "A5").Value2 = "Ngày báo cáo: " + time;

                int j = 6;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A7", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;

                ClsBaocaotonghopDAL ClsBaocaotonghopdal = new ClsBaocaotonghopDAL();
                System.Data.DataTable Tbllop = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT TblLop.Tenlop,TblLop.id, TblDm_Khoilop.Tenkhoilop FROM (TblLop INNER JOIN TblDm_Phanloailop ON TblLop.PhanloailopId = TblDm_Phanloailop.Id) INNER JOIN TblDm_Khoilop ON TblDm_Phanloailop.KhoilopID = TblDm_Khoilop.Id WHERE TblLop.NamhocId = " + namid);
                System.Data.DataTable Tbltrebylop;
                System.Data.DataTable Tbltrecualop;
                for (int e = 0; e <= Tbllop.Rows.Count - 1; e++)
                {
                    if (e != Tbllop.Rows.Count - 1)
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    oSheet.get_Range("B" + (7 + e), "B" + (7 + e)).Value2 = Tbllop.Rows[e]["tenlop"].ToString();

                    Tbltrebylop = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT TblAntrongngay.Id AS TreantrongngayId, TblTrediemdanh.Id AS TredihocId, TblAntrongngay.NamtrehocId, TblAntrongngay.LophocId, TblAntrongngay.Hovaten, TblAntrongngay.Tenthuonggoi, TblAntrongngay.Ngaysinh, TblAntrongngay.Gioitinh, TblAntrongngay.Tendantoc, TblAntrongngay.Trangthai AS Diemdanhan, TblTrediemdanh.Trangthai AS Diemdanhdihoc, iif(isnull(TblAntrongngay.Ngay),#" + ngay.ToShortDateString() + "#,TblAntrongngay.Ngay) AS Ngay, iif(isnull(  TblAntrongngay.ThanghocId)," + thang + ",TblAntrongngay.ThanghocId) AS ThanghocId FROM [SELECT TblTredihoc.Id, Vw_Tre_namtrehoc.NamtrehocId, Vw_Tre_namtrehoc.LophocId, Vw_Tre_namtrehoc.Hovaten, Vw_Tre_namtrehoc.Tenthuonggoi,  Vw_Tre_namtrehoc.Ngaysinh, Vw_Tre_namtrehoc.Gioitinh, Vw_Tre_namtrehoc.Tendantoc, TblTredihoc.Ngay, TblTredihoc.Trangthai,TblTredihoc.ThanghocId   FROM Vw_Tre_namtrehoc LEFT JOIN (SELECT TblDiemdanhtrehoc.Id, TblDiemdanhtrehoc.NamtrehocId, TblDiemdanhtrehoc.Ngay, TblDiemdanhtrehoc.ThanghocId, TblDiemdanhtrehoc.Trangthai  FROM TblDiemdanhtrehoc   WHERE (((TblDiemdanhtrehoc.Ngay)=#" + ngay.ToShortDateString() + "#) AND ((TblDiemdanhtrehoc.ThanghocId)=" + thang + "))) AS TblTredihoc ON Vw_Tre_namtrehoc.NamtrehocId = TblTredihoc.NamtrehocId  WHERE (((Vw_Tre_namtrehoc.LophocId)=" + Tbllop.Rows[e]["id"].ToString() + "))]. AS TblTrediemdanh INNER JOIN [SELECT  TblTrethamgiaan.Id, Vw_Tre_namtrehoc.NamtrehocId, Vw_Tre_namtrehoc.LophocId, Vw_Tre_namtrehoc.Hovaten, Vw_Tre_namtrehoc.Tenthuonggoi, Vw_Tre_namtrehoc.Ngaysinh, Vw_Tre_namtrehoc.Gioitinh, Vw_Tre_namtrehoc.Tendantoc, TblTrethamgiaan.Trangthai, TblTrethamgiaan.Ngay, TblTrethamgiaan.ThanghocId, Vw_Tre_namtrehoc.NamhocId  FROM Vw_Tre_namtrehoc LEFT JOIN (SELECT TblDiemdanhtrean.Id, TblDiemdanhtrean.NamtrehocId, TblDiemdanhtrean.Ngay, TblDiemdanhtrean.ThanghocId, TblDiemdanhtrean.Trangthai  FROM TblDiemdanhtrean  WHERE ( ((TblDiemdanhtrean.Ngay)=#" + ngay.ToShortDateString() + "#) AND ((TblDiemdanhtrean.ThanghocId)=" + thang + "))) AS TblTrethamgiaan ON Vw_Tre_namtrehoc.NamtrehocId = TblTrethamgiaan.NamtrehocId WHERE (((Vw_Tre_namtrehoc.LophocId)=" + Tbllop.Rows[e]["id"].ToString() + "))]. AS TblAntrongngay ON TblTrediemdanh.NamtrehocId = TblAntrongngay.NamtrehocId where TblAntrongngay.Trangthai like '-1'");
                    Tbltrecualop = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("SELECT TblAntrongngay.Id AS TreantrongngayId, TblTrediemdanh.Id AS TredihocId, TblAntrongngay.NamtrehocId, TblAntrongngay.LophocId, TblAntrongngay.Hovaten, TblAntrongngay.Tenthuonggoi, TblAntrongngay.Ngaysinh, TblAntrongngay.Gioitinh, TblAntrongngay.Tendantoc, TblAntrongngay.Trangthai AS Diemdanhan, TblTrediemdanh.Trangthai AS Diemdanhdihoc, iif(isnull(TblAntrongngay.Ngay),#" + ngay.ToShortDateString() + "#,TblAntrongngay.Ngay) AS Ngay, iif(isnull(  TblAntrongngay.ThanghocId)," + thang + ",TblAntrongngay.ThanghocId) AS ThanghocId FROM [SELECT TblTredihoc.Id, Vw_Tre_namtrehoc.NamtrehocId, Vw_Tre_namtrehoc.LophocId, Vw_Tre_namtrehoc.Hovaten, Vw_Tre_namtrehoc.Tenthuonggoi,  Vw_Tre_namtrehoc.Ngaysinh, Vw_Tre_namtrehoc.Gioitinh, Vw_Tre_namtrehoc.Tendantoc, TblTredihoc.Ngay, TblTredihoc.Trangthai,TblTredihoc.ThanghocId   FROM Vw_Tre_namtrehoc LEFT JOIN (SELECT TblDiemdanhtrehoc.Id, TblDiemdanhtrehoc.NamtrehocId, TblDiemdanhtrehoc.Ngay, TblDiemdanhtrehoc.ThanghocId, TblDiemdanhtrehoc.Trangthai  FROM TblDiemdanhtrehoc   WHERE (((TblDiemdanhtrehoc.Ngay)=#" + ngay.ToShortDateString() + "#) AND ((TblDiemdanhtrehoc.ThanghocId)=" + thang + "))) AS TblTredihoc ON Vw_Tre_namtrehoc.NamtrehocId = TblTredihoc.NamtrehocId  WHERE (((Vw_Tre_namtrehoc.LophocId)=" + Tbllop.Rows[e]["id"].ToString() + "))]. AS TblTrediemdanh INNER JOIN [SELECT  TblTrethamgiaan.Id, Vw_Tre_namtrehoc.NamtrehocId, Vw_Tre_namtrehoc.LophocId, Vw_Tre_namtrehoc.Hovaten, Vw_Tre_namtrehoc.Tenthuonggoi, Vw_Tre_namtrehoc.Ngaysinh, Vw_Tre_namtrehoc.Gioitinh, Vw_Tre_namtrehoc.Tendantoc, TblTrethamgiaan.Trangthai, TblTrethamgiaan.Ngay, TblTrethamgiaan.ThanghocId, Vw_Tre_namtrehoc.NamhocId  FROM Vw_Tre_namtrehoc LEFT JOIN (SELECT TblDiemdanhtrean.Id, TblDiemdanhtrean.NamtrehocId, TblDiemdanhtrean.Ngay, TblDiemdanhtrean.ThanghocId, TblDiemdanhtrean.Trangthai  FROM TblDiemdanhtrean  WHERE ( ((TblDiemdanhtrean.Ngay)=#" + ngay.ToShortDateString() + "#) AND ((TblDiemdanhtrean.ThanghocId)=" + thang + "))) AS TblTrethamgiaan ON Vw_Tre_namtrehoc.NamtrehocId = TblTrethamgiaan.NamtrehocId WHERE (((Vw_Tre_namtrehoc.LophocId)=" + Tbllop.Rows[e]["id"].ToString() + "))]. AS TblAntrongngay ON TblTrediemdanh.NamtrehocId = TblAntrongngay.NamtrehocId");

                    oSheet.get_Range("A" + (7 + e), "A" + (7 + e)).Value2 = e + 1;

                    oSheet.get_Range("C" + (7 + e), "C" + (7 + e)).Value2 = Tbltrebylop.Rows.Count;
                    oSheet.get_Range("D" + (7 + e), "D" + (7 + e)).Value2 = Tbltrecualop.Rows.Count;
                    oSheet.get_Range("E" + (7 + e), "E" + (7 + e)).Value2 = ngay;

                    if (Tbllop.Rows[e]["tenkhoilop"].ToString() == "Mẫu giáo")
                    {
                        mg_ba += Tbltrebylop.Rows.Count;
                        tre_mg += Tbltrecualop.Rows.Count;
                    }
                    if (Tbllop.Rows[e]["tenkhoilop"].ToString() == "Nhà trẻ")
                    {
                        nt_ba += Tbltrebylop.Rows.Count;
                        tre_nt += Tbltrecualop.Rows.Count;
                    }

                }

                oSheet.get_Range("C" + (9 + Tbllop.Rows.Count), "C" + (9 + Tbllop.Rows.Count)).Value2 = mg_ba;
                oSheet.get_Range("D" + (9 + Tbllop.Rows.Count), "D" + (9 + Tbllop.Rows.Count)).Value2 = tre_mg;
                oSheet.get_Range("C" + (10 + Tbllop.Rows.Count), "C" + (10 + Tbllop.Rows.Count)).Value2 = nt_ba;
                oSheet.get_Range("D" + (10 + Tbllop.Rows.Count), "D" + (10 + Tbllop.Rows.Count)).Value2 = tre_nt;
                oSheet.get_Range("C" + (11 + Tbllop.Rows.Count), "C" + (11 + Tbllop.Rows.Count)).Value2 = nt_ba + mg_ba;
                oSheet.get_Range("D" + (11 + Tbllop.Rows.Count), "D" + (11 + Tbllop.Rows.Count)).Value2 = tre_nt + tre_mg;
                oSheet.PageSetup.CenterHorizontally = true;

            }
            catch (Exception ex)
            { }
        }

        //public static void hoadon_ngoaikhoa(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, DateTime ngay, int namtrehoc, int thang, int nam, string lop)
        //{
        //    try
        //    {
        //        string ExecelFileTemplate = SaveResouceFile(FileName, stream);
        //        KillExcelProcess();
        //        Microsoft.Office.Interop.Excel.Application oXL = null;
        //        _Workbook oWB = null;
        //        _Worksheet oSheet = null;
        //        GC.Collect();
        //        oXL = new ApplicationClass();
        //        //     oXL.Quit();
        //        oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

        //        oSheet = (_Worksheet)oWB.Sheets["Sheet1"];
        //        Execute_hd(oSheet, tenso, tenphong, tentruong, tieude, ngay, namtrehoc, thang, nam, lop);
        //        oXL.Visible = true;
        //    }
        //    catch
        //    {
        //    }
        //}
        //private static void Execute_hd(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, DateTime ngay, int namtrehoc, int ThanghocId, int NamhocId, string lop)
        //{
        //    ClsBaocaotonghopDAL ClsBaocaotonghopdal = new ClsBaocaotonghopDAL();
        //    ClsTblDm_NgoaikhoaDAL ClsTblDm_Ngoaikhoadal = new ClsTblDm_NgoaikhoaDAL();

        //    System.Data.DataTable Tbl = new System.Data.DataTable();
        //    System.Data.DataTable Tblmucnk = new System.Data.DataTable();

        //    string NamePrc, SPROC_TBLTrethamgianngoaikhoa_GET2;
        //    System.Data.DataTable dt = ClsTblDm_Ngoaikhoadal.GetAll();
        //    string sSelect = "";

        //    for (int i = 0; i < dt.Rows.Count; i++)
        //        //         sSelect += ",(select iif(isnull(Tienngoaikhoa),Tienngoaikhoa,1)  from TblTrethungoaikhoa where Vw_Tre_namtrehoc.NamtrehocId = TblTrethungoaikhoa.NamtrehocId and ((TblTrethungoaikhoa.ThanghocId)=" + ThanghocId + ") and  (TblTrethungoaikhoa.Ngayngoaikhoa =#" + ngay.ToShortDateString() + "#) AND ((TblTrethungoaikhoa.NgoaikhoaId)=" + dt.Rows[i]["ID"] + "))  as " + dt.Rows[i]["ID"] + " ";
        //        sSelect += ",(select Count(Tienngoaikhoa)  from TblTrethungoaikhoa where Vw_Tre_namtrehoc.NamtrehocId = TblTrethungoaikhoa.NamtrehocId and ((TblTrethungoaikhoa.ThanghocId)=" + ThanghocId + ")  AND ((TblTrethungoaikhoa.NgoaikhoaId)=" + dt.Rows[i]["ID"] + "))  as " + dt.Rows[i]["ID"] + " ";

        //    SPROC_TBLTrethamgianngoaikhoa_GET2 = ""
        //      + "SELECT  Vw_Tre_namtrehoc.Hovaten,TblLop.Tenlop"
        //      + sSelect
        //      + " FROM Vw_Tre_namtrehoc INNER JOIN TblLop ON Vw_Tre_namtrehoc.LophocId = TblLop.Id "
        //      + " WHERE Vw_Tre_namtrehoc.NamhocId=" + NamhocId + " and Vw_Tre_namtrehoc.namtrehocid = " + namtrehoc;

        //    NamePrc = SPROC_TBLTrethamgianngoaikhoa_GET2;
        //    Tbl = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2(NamePrc);

        //    try
        //    {
        //        oSheet.Cells[1, 1] = tenso;
        //        oSheet.Cells[2, 1] = tenphong;
        //        oSheet.Cells[3, 1] = tentruong;

        //        oSheet.get_Range("A5", "A5").Value2 = tieude;

        //        string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();
        //        //oSheet.get_Range("A5", "A5").Value2 = "Lớp: " + lop;
        //        oSheet.get_Range("A6", "A6").Value2 = "Số HD: " + namtrehoc;
        //        oSheet.get_Range("A7", "A7").Value2 = "Ngày thu: " + time;

        //        oSheet.get_Range("A8", "A8").Value2 = "Họ và tên: " + Tbl.Rows[0]["Hovaten"].ToString() + "       Lớp: " + Tbl.Rows[0]["Tenlop"].ToString();

        //        Tbl.Columns.Remove("Hovaten");
        //        Tbl.Columns.Remove("Tenlop");
        //        int j = 9;
        //        string a = "A";
        //        const int xlShiftDown = -4121;
        //        Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A10", Missing.Value);
        //        Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;

        //        NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat;
        //        nfi.NumberGroupSeparator = ".";
        //        nfi.NumberDecimalSeparator = ",";



        //        //    int tg = 0;
        //        int tong = 0;
        //        for (int e = 0; e <= Tbl.Columns.Count - 1; e++)
        //        {

        //            Tblmucnk = ClsBaocaotonghopdal.Tblbaocaothongke_Daunam_mau2("select tenmuc, tienphainop from tbldm_ngoaikhoa where id = " + int.Parse(Tbl.Columns[e].ColumnName.ToString()));
        //            int s; string tg = ""; string test = "0"; string test2 = "0"; string tg2 = ""; int s2;
        //            if (Tbl.Rows[0][e].ToString() != "0")
        //            {

        //                oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);
        //                j = j + 1;

        //                oSheet.get_Range("B" + j.ToString(), "B" + j.ToString()).Value2 = Tblmucnk.Rows[0]["Tenmuc"].ToString();
        //                oSheet.get_Range("A" + j.ToString(), "A" + j.ToString()).Value2 = "= Row()- 9";
        //                if (Tblmucnk.Rows[0]["Tienphainop"].ToString() != "")
        //                    test = Tblmucnk.Rows[0]["Tienphainop"].ToString();
        //                s = int.Parse(test);

        //                tg = s.ToString("N0", nfi);
        //                oSheet.get_Range("C" + j.ToString(), "C" + j.ToString()).Value2 = tg;

        //                oSheet.get_Range("D" + j.ToString(), "D" + j.ToString()).Value2 = Tbl.Rows[0][e].ToString();
        //                if ((int.Parse(Tblmucnk.Rows[0]["Tienphainop"].ToString()) * int.Parse(Tbl.Rows[0][e].ToString())).ToString() != "")
        //                    test2 = (int.Parse(Tblmucnk.Rows[0]["Tienphainop"].ToString()) * int.Parse(Tbl.Rows[0][e].ToString())).ToString();
        //                s2 = int.Parse(test2);
        //                tg2 = s2.ToString("N0", nfi);
        //                oSheet.get_Range("E" + j.ToString(), "E" + j.ToString()).Value2 = tg2;

        //                tong = tong + int.Parse(Tblmucnk.Rows[0]["Tienphainop"].ToString()) * int.Parse(Tbl.Rows[0][e].ToString());
        //                //  tg += 1;
        //            }

        //        }

        //        Microsoft.Office.Interop.Excel.Range rang = oSheet.get_Range("A" + (1 + j), Missing.Value).EntireRow;
        //        rang.Delete(XlDeleteShiftDirection.xlShiftUp);
        //        rang = oSheet.get_Range("E" + (1 + j), "E" + (1 + j));
        //        //     rang.Value2 = "=Sum(E10:E"+j+")";
        //        rang.Value2 = tong.ToString("N0", nfi);

        //        rang = oSheet.get_Range("a" + (3 + j), "a" + (3 + j));
        //        rang.Value2 = "(Bằng chữ: " + QLMN.Common.Util.docso(tong.ToString()) + ")";

        //        rang = oSheet.get_Range("a1", "e" + (5 + j));
        //        Microsoft.Office.Interop.Excel.Range rang_ = oSheet.get_Range("A" + (j + 11), "A" + (j + 11));

        //        rang.Copy(rang_);

        //    }
        //    catch (Exception ex)
        //    { }
        //}

        public static void Ds_thucpham(Stream stream, string FileName, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string lop)
        {
            try
            {
                string ExecelFileTemplate = SaveResouceFile(FileName, stream);
                KillExcelProcess();
                Microsoft.Office.Interop.Excel.Application oXL = null;
                _Workbook oWB = null;
                _Worksheet oSheet = null;
                GC.Collect();
                oXL = new ApplicationClass();
                oWB = oXL.Workbooks.Open(ExecelFileTemplate, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                oSheet = (_Worksheet)oWB.Sheets["Sheet1"]; oXL.Visible = true;
                Execute_ds_thucpham(oSheet, tenso, tenphong, tentruong, tieude, Tblnhanvien, "");

            }
            catch
            {
            }
        }
        private static void Execute_ds_thucpham(Microsoft.Office.Interop.Excel._Worksheet oSheet, string tenso, string tenphong, string tentruong, string tieude, System.Data.DataTable Tblnhanvien, string lop)
        {

            System.Data.DataTable TblBaocaothongke = new System.Data.DataTable();
            TblBaocaothongke = Tblnhanvien;
            //            int cn_bt = 0;
            //            int cn_sdd1 = 0; int cn_sdd2 = 0; int cn_ncbp1 = 0; int cn_ncbp2 = 0; int cc_bt = 0; int cc_tc1 = 0; int cc_tc2 = 0;
            try
            {
                oSheet.Cells[1, 1] = tenso;
                oSheet.Cells[2, 1] = tenphong;
                oSheet.Cells[3, 1] = tentruong;

                oSheet.get_Range("A4", "A4").Value2 = tieude;

                string time = DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString();

                oSheet.get_Range("A5", "A5").Value2 = "Ngày báo cáo: " + time;

                Microsoft.Office.Interop.Excel.Range rang;
                int j = 7;
                string a = "A";
                const int xlShiftDown = -4121;
                Microsoft.Office.Interop.Excel.Range oRange = oSheet.get_Range("A8", Missing.Value);
                Microsoft.Office.Interop.Excel.Range oRow = oRange.EntireRow;
                bool tg = true;
                for (int i = 0; i < TblBaocaothongke.Rows.Count; i++)
                {
                    j = j + 1;
                    if (i != (TblBaocaothongke.Rows.Count - 1))
                        oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

                    for (int b = 0; b < TblBaocaothongke.Columns.Count; b++)
                    {
                        if (a != "U")
                        {
                            oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = TblBaocaothongke.Rows[i][b];
                        }
                        else
                        {
                            if (bool.Parse(TblBaocaothongke.Rows[i][b].ToString()) == true)
                                oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = "ĐV";
                            else
                                oSheet.get_Range(a + j.ToString(), a + j.ToString()).Value2 = "TV";

                        }
                        rang = oSheet.get_Range(a + j.ToString(), a + j.ToString());
                        if (tg == false)
                        {
                            rang.Interior.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.LightYellow);
                        }
                        a = ExcelFunc.NextChar(a);

                    }
                    a = "A";
                    if (tg == false)
                    {
                        tg = true;
                    }
                    else
                    {
                        tg = false;
                    }

                    //Range rang = oSheet.get_Range("K" + j.ToString(), "M" + j.ToString());
                    //rang.Merge(false);

                }

                for (int m = 3; m <= 19; m++)
                {
                    if (m != 10 && m != 16)
                        AutoFitColumn(m, oSheet);
                }
                rang = oSheet.get_Range("A8", "A8");
                rang.Activate();
                rang.Application.ActiveWindow.FreezePanes = true;

            }
            catch (Exception ex)
            { }
        }

    }
}
